<?php

/**
 * MVM_MALL 网上商店系统 excel文件下载
 * ============================================================================
 * 版权所有 (C) 2007-2010 www.mvmmall.com，并保留所有权利。
 * 网站地址: http://www.mvmmall.com
 * ----------------------------------------------------------------------------
 * 这是一个免费开源的软件；这意味着您可以在不用于商业目的的前提下对程序代码
 * 进行修改、使用和再发布。
 * ============================================================================
 * $Author:  www.mvmmall.com  $
 * $Date: 2008-02-03 $
 * $Id: excel.inc.php  www.mvmmall.com$
 * ---------------------------------------------
*/

if(!defined('MVMMALL') || !defined('IN_ADMINCP')) exit('Access Denied');
if($amode == "make_excel" or $amode == "all_excel") {

	if($amode == "make_excel") {
        $start_date = mktime(0,0,0,$start_mm,$start_dd,$start_yy);
        $end_date = mktime(23,59,59,$end_mm,$end_dd,$end_yy);
        if($ps_duse != "1") {
            $search_sql = " addtime >= '$start_date' and addtime <= '$end_date' ";
        } else {
            $search_sql = " addtime >'0' ";
        } 
        if($ordersn) { 
            $ordersn = dhtmlchars($ordersn); 
            $search_sql .= "  and ordersn = '$ordersn' "; 
        }
        if($ps_email){ 
            $ps_email = dhtmlchars($ps_email);
            $search_sql .= " and email='$ps_email' "; 
        }
        
        if($ps_name) { 
             $ps_name = dhtmlchars($ps_name);
            $search_sql .= " and consignee='$ps_name' "; 
        }
	}
	if($search_sql) $search_sql = "WHERE ".$search_sql;
    $result = $db->query("SELECT * FROM {$tablepre}order_info $search_sql ORDER BY `uid` DESC");
    $excel_save = "订单号,日期,姓名,区分会员,";
    $excel_save .= "订货状态,结算方式,汇款人,汇款银行,汇款日,总购买金额,总结算金额,总赠送积分,";
    $excel_save .= "电话号码,手机号码,邮编,地址,邮件,";
	$excel_save .= "收货人姓名,收货人电话,收货人手机号码,收货人邮编,收货人地址,配送方式,包装方式,折扣,其他要求,";
	$excel_save .= "商品名(型号)(可选1 可选2 可选3 可选4 可选5 可选6 可选7) 单价 * 数量 = 合计(积分合计)\n";

	while($list = $db->fetch_array($result)) {

	    $show_date                 = date("Y-m-d",$list[register_date]);
		$show_rdate                = date("Y-m-d",$list[receipt_date]);
        $show_total                = $list[buyer_price]+$list[buyer_delivery]+$list[buyer_wrapping];
		$show_account = $list[buyer_price]+$list[buyer_delivery]+$list[buyer_wrapping]-$list[buyer_discount]-$list[buyer_use_point];
        if($list[buyer_id]) $show_id = "会员($list[buyer_id])"; else $show_id = "非会员";
		$list[buyer_main]          = comma_del(enter_del($list[buyer_main]));
		$list[buyer_delivery_info] = comma_del(enter_del($list[buyer_delivery_info]));
		$list[buyer_wrapping_info] = comma_del(enter_del($list[buyer_wrapping_info]));
		$list[buyer_discount_info] = comma_del(enter_del($list[buyer_discount_info]));

		if($list[buyer_payment] == "1") $show_payment = "信用卡结算"; else $show_payment = "在线结算";
      
        if($list[buyer_state] == "0") $list[buyer_state] = "1";


	    $excel_save .=  "$list[buyer_sess],$show_date,$list[buyer_name1],$show_id,";
	    $excel_save .= $m_order_array[$list[buyer_state]].",$show_payment,$list[buyer_name2],$list[buyer_bank],$show_rdate,$show_total,$show_account,$list[buyer_point],";
	    $excel_save .= "$list[buyer_tel1],$list[buyer_tel2],$list[buyer_zip1],$list[buyer_address1],$list[buyer_email],";
	    $excel_save .= "$list[buyer_name3],$list[buyer_tel3],$list[buyer_tel4],$list[buyer_zip2],$list[buyer_address2],$list[buyer_delivery_info],$list[buyer_wrapping_info],$list[buyer_discount_info],$list[buyer_main],";



		$pu_result = $db->query("select * from {$tablepre}purchase_table where purchase_sess = '$list[buyer_sess]' order by register_date asc");

		while($pu_list = $db->fetch_array($pu_result)) {

			  $go_list = $db->fetch_array($db->query("select * from {$tablepre}goods_table where uid = '$pu_list[purchase_goods_id]'"));


			  $show_price = make_price_format($pu_list[purchase_price] * $pu_list[purchase_num],0);
			  $show_point = make_price_format($pu_list[purchase_point] * $pu_list[purchase_num],0);
			  $pu_list[purchase_price] = make_price_format($pu_list[purchase_price],0);
			  $pu_list[purchase_point] = make_price_format($pu_list[purchase_point],0);
			  $pu_list[purchase_subject] = comma_del(enter_del($pu_list[purchase_subject]));
			  $pu_list[purchase_option1] = comma_del(enter_del($pu_list[purchase_option1]));
 
	          $excel_add .=  "$pu_list[purchase_subject]($go_list[goods_code])($pu_list[purchase_option1] $pu_list[purchase_option2] $pu_list[purchase_option3] $pu_list[purchase_option4] $pu_list[purchase_option5] $pu_list[purchase_option6] $pu_list[purchase_option7])  $pu_list[purchase_price] * $pu_list[purchase_num] = $show_price($show_point)     ";	
        
		}

	          $excel_save .=  $excel_add."\n";
			  $excel_add = "";

	}

	$excel_file = "order_list_".$amode.$m_now_counter.".csv";
    header("Content-Type: application/vnd.ms-excel");   
    header("Content-Disposition: attachment; filename=$excel_file");
	
	echo $excel_save;

} elseif($amode == "member_excel" or $amode == "allmember_excel") {

	if($amode == "member_excel") {
		$start_date = mktime(0,0,0,$start_mm,$start_dd,$start_yy);
		$end_date = mktime(23,59,59,$end_mm,$end_dd,$end_yy);
		
		if($ps_duse != "1") {
			$serch_sql = " where register_date >= '$start_date' and register_date <= '$end_date' ";
		} else {
			$serch_sql = " where register_date > 0 ";
		}

		if($ps_name1) { $serch_sql .= " and member_name like '%$ps_name1%' "; }
		if($ps_jumin) { $serch_sql = " where member_jumin = '$ps_jumin' ";}
		if($ps_email) { $serch_sql .= " and member_email like '%$ps_email%' "; }
		if($ps_member) { $serch_sql .= " and member_id like '%$ps_member' "; }
		if($ps_sex) { 
			$serch_sql .= " and ( member_sex = '$ps_sex' or substring(member_jumin,7,1) = '$ps_sex') "; 
		}
		if($ps_tel) { $serch_sql .= " and member_tel1 like '%$ps_tel%' "; }
		if($ps_address) { $serch_sql .= " and member_address like '%$ps_address%' "; }
		if($ps_name2) { $serch_sql .= " and member_recommend like '%$ps_name2%' "; }
	}

	$query = "select * from {$tablepre}member_table $serch_sql order by uid desc";
    $result = $db->query($query);

    $excel_save = "会员号,会员等级,会员用户名,会员姓名,";
    $excel_save .= "会员性别,会员出生,电话号码,手机号码,邮件,个人主页,";
    $excel_save .= "邮编,家庭地址,接收邮件,推荐人,";
	$excel_save .= "关心领域,爱好,宗教,血型,职业,公司名,部门,公司电话,公司邮编,公司地址,";
	$excel_save .= "结婚与否,结婚纪念日,个人介绍,留言,管理员MEMO,会员积分,登陆次数,加入日期\n";

	while($list = $db->fetch_array($result)) {

		if($list[member_sex] == "1") $list[member_sex] = "男"; else $list[member_sex] = "女";
        
		$list[member_class]        = $m_class_array[$list[member_class]];
		$list[member_address]      = comma_del(enter_del($list[member_address]));
		$list[member_introduction] = comma_del(enter_del($list[member_introduction]));
		$list[member_admin_memo]   = comma_del(enter_del($list[member_admin_memo]));

        $excel_save .= "$list[uid],$list[member_class],$list[member_id],$list[member_name],";
        $excel_save .= "$list[member_sex],$list[member_birthday],$list[member_tel1],$list[member_tel2],$list[member_email],$list[member_homepage],";
        $excel_save .= "$list[member_zip],$list[member_address],$list[member_automail],$list[member_recommend],";
	    $excel_save .= "$list[member_job],$list[member_jobname1],$list[member_jobname2],$list[member_tel4],$list[member_jobzip],$list[member_jobaddress],";
	    $excel_save .= "$list[member_introduction],$list[member_main],$list[member_admin_memo],$list[member_point],$list[member_login],$list[register_date]\n";
	}

	$excel_file = "member_list".$m_now_counter.".csv";
    header("Content-Type: application/vnd.ms-excel");   
    header("Content-Disposition: attachment; filename=$excel_file");
	
	echo $excel_save;

} elseif($amode == "delivery_excel") {

	if($ps_name1 or $ps_order or $ps_email or $ps_member or $ps_name2 or $ps_name3 or $ps_state1 or $ps_state2) {
        $start_date = mktime(0,0,0,$start_mm,$start_dd,$start_yy);
        $end_date = mktime(23,59,59,$end_mm,$end_dd,$end_yy);
    
	    if($ps_duse != "1") {
            $serch_sql = " where register_date >= '$start_date' and register_date <= '$end_date' ";
	    } else {
	        $serch_sql = " where register_date > 0 ";
	    }

        if($ps_name1) { $serch_sql .= " and buyer_name1 like '%$ps_name1%' "; }
	    if($ps_order) { $serch_sql = " where buyer_sess = '$ps_order' "; }
	    if($ps_email) { $serch_sql .= " and buyer_email like '%$ps_email%' "; }
	    if($ps_member) { $serch_sql .= " and buyer_id like '%$ps_member' "; }
	    if($ps_name2) { $serch_sql .= " and buyer_name2 like '%$ps_name2%' "; }
	    if($ps_name3) { $serch_sql .= " and buyer_name3 like '%$ps_name3%' "; }
	    if($ps_state1) { $serch_sql .= " and buyer_state >= $ps_state1 "; }
	    if($ps_state2) { $serch_sql .= " and buyer_state <= $ps_state2 "; }
	} else {
	    $serch_sql = "where buyer_state = 2";
	}

	$query = "select * from {$tablepre}buyer_table where buyer_state = 2 order by uid desc";
    $result = $db->query($query);

    $excel_save = "编号,订单号,订货人,商品名,订货人电话1,订货人电话2,";
	$excel_save .= "收取人,收取人电话1,收取人电话2,邮编,地址,备注\n";


	while($list = $db->fetch_array($result)) {

		$list[buyer_main]          = comma_del(enter_del($list[buyer_main]));

		$pu_result = $db->query("select * from {$tablepre}purchase_table where purchase_sess = '$list[buyer_sess]' order by register_date asc");
		
		if($list[buyer_zip2] and $list[buyer_address2]) {
		   //$zip = substr($list[buyer_zip2],0,3)."-".substr($list[buyer_zip2],3,3);
		   $zip = $list[buyer_zip2];
		   $address = $list[buyer_address2];
		} else {
		   //$zip = substr($list[buyer_zip1],0,3)."-".substr($list[buyer_zip1],3,3);
		   $zip = $list[buyer_zip1];
		   $address = $list[buyer_address1];		
		}

		if(!$list[buyer_name3]) $list[buyer_name3] = $list[buyer_name1];
		if(!$list[buyer_tel3]) $list[buyer_tel3] = $list[buyer_tel1];
		if(!$list[buyer_tel4]) $list[buyer_tel4] = $list[buyer_tel2];

		while($pu_list = $db->fetch_array($pu_result)) {

			  $go_list = $db->fetch_array($db->query("select * from {$tablepre}goods_table where uid = '$pu_list[purchase_goods_id]'"));
			  
			  $pu_list[purchase_subject] = comma_del(enter_del($pu_list[purchase_subject]));
			  $go_list[goods_code] = comma_del(enter_del($go_list[goods_code]));
			  $go_list[goods_company] = comma_del(enter_del($go_list[goods_company]));

			  $goods_bitlist .= "$pu_list[purchase_subject]($go_list[goods_code]$go_list[goods_company])X$pu_list[purchase_num]";
			  
        
		}
		      $co++;
	          $excel_save .=  "$co,$list[buyer_sess],$list[buyer_name1],$goods_bitlist,$list[buyer_tel1],$list[buyer_tel2],";
	          $excel_save .= "$list[buyer_name3],$list[buyer_tel3],$list[buyer_tel4],";
	          $excel_save .= "$zip,$address,$list[buyer_main]\n";

			  $goods_bitlist = "";
	}

	$excel_file = "delivery_list".$m_now_counter.".csv";
    header("Content-Type: application/vnd.ms-excel");   
    header("Content-Disposition: attachment; filename=$excel_file");
	
	echo $excel_save;

}
?>
